import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ArrayNode;
import com.fasterxml.jackson.databind.node.ObjectNode;

import java.io.*;
import java.util.*;

public class ExcelToJsonByTemplate1{

    private final ObjectMapper objectMapper = new ObjectMapper();

    /**
     * 按照模板格式转换Excel为JSON
     */
    public String convertExcelByTemplate(String filePath) throws Exception {
        try (FileInputStream file = new FileInputStream(filePath);
             Workbook workbook = new HSSFWorkbook(file)) {

            ObjectNode result = objectMapper.createObjectNode();
            ArrayNode tables = objectMapper.createArrayNode();

            // 处理所有工作表
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheet = workbook.getSheetAt(i);
                ObjectNode tableData = convertSheetByTemplate(sheet);
                tables.add(tableData);
            }

            result.set("tables", tables);
            return objectMapper.writerWithDefaultPrettyPrinter().writeValueAsString(result);
        }
    }

    /**
     * 按照模板格式转换单个工作表
     */
    private ObjectNode convertSheetByTemplate(Sheet sheet) {
        ObjectNode tableNode = objectMapper.createObjectNode();
        tableNode.put("name", sheet.getSheetName());

        // 处理合并单元格区域
        ArrayNode mergedRegionsArray = objectMapper.createArrayNode();
        List<CellRangeAddress> mergedRegions = sheet.getMergedRegions();

        for (CellRangeAddress mergedRegion : mergedRegions) {
            ObjectNode regionNode = objectMapper.createObjectNode();
            regionNode.put("range", getRangeString(mergedRegion));
            regionNode.put("firstRow", mergedRegion.getFirstRow());
            regionNode.put("lastRow", mergedRegion.getLastRow());
            regionNode.put("firstCol", mergedRegion.getFirstColumn());
            regionNode.put("lastCol", mergedRegion.getLastColumn());

            // 获取主单元格信息
            Row firstRow = sheet.getRow(mergedRegion.getFirstRow());
            if (firstRow != null) {
                Cell firstCell = firstRow.getCell(mergedRegion.getFirstColumn());
                if (firstCell != null) {
                    regionNode.put("value", getCellValueAsString(firstCell));
                    regionNode.set("style", extractStyleInfo(firstCell));
                }
            }

            mergedRegionsArray.add(regionNode);
        }
        tableNode.set("mergedRegions", mergedRegionsArray);

        // 处理单元格数据
        ArrayNode cellsArray = objectMapper.createArrayNode();
        Map<String, CellRangeAddress> mergeMap = createMergeMap(mergedRegions);

        for (int rowNum = sheet.getFirstRowNum(); rowNum <= sheet.getLastRowNum(); rowNum++) {
            Row row = sheet.getRow(rowNum);
            if (row == null) continue;

            for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
                Cell cell = row.getCell(colNum);
                String cellKey = rowNum + "_" + colNum;

                ObjectNode cellNode = objectMapper.createObjectNode();
                cellNode.put("row", rowNum);
                cellNode.put("col", colNum);
                cellNode.put("address", getCellAddress(rowNum, colNum));

                // 处理合并单元格
                if (mergeMap.containsKey(cellKey)) {
                    CellRangeAddress mergedRegion = mergeMap.get(cellKey);
                    cellNode.put("merged", true);
                    cellNode.put("mergeMaster",
                            mergedRegion.getFirstRow() == rowNum &&
                                    mergedRegion.getFirstColumn() == colNum);
                    cellNode.put("mergeRange", getRangeString(mergedRegion));

                    // 获取主单元格的值和样式
                    Row masterRow = sheet.getRow(mergedRegion.getFirstRow());
                    Cell masterCell = masterRow.getCell(mergedRegion.getFirstColumn());
                    if (masterCell != null) {
                        cellNode.put("value", getCellValueAsString(masterCell));
                        cellNode.set("style", extractStyleInfo(masterCell));
                    }
                } else if (cell != null) {
                    // 普通单元格
                    cellNode.put("merged", false);
                    cellNode.put("mergeMaster", false);
                    cellNode.put("value", getCellValueAsString(cell));
                    cellNode.set("style", extractStyleInfo(cell));
                } else {
                    // 空单元格
                    cellNode.put("merged", false);
                    cellNode.put("mergeMaster", false);
                    cellNode.put("value", "");
                    cellNode.set("style", objectMapper.createObjectNode());
                }

                cellsArray.add(cellNode);
            }
        }

        tableNode.set("cells", cellsArray);

        // 添加表格元数据
        tableNode.put("totalRows", sheet.getLastRowNum() + 1);
        tableNode.put("totalColumns", getMaxColumnCount(sheet));

        return tableNode;
    }

    /**
     * 提取样式信息（按照您提供的格式）
     */
    private ObjectNode extractStyleInfo(Cell cell) {
        ObjectNode styleNode = objectMapper.createObjectNode();
        if (cell == null) return styleNode;

        CellStyle cellStyle = cell.getCellStyle();

//        try {
//            // 背景颜色
//            Color bgColor = cellStyle.getFillForegroundColorColor();
//            if (bgColor instanceof HSSFColor) {
//                HSSFColor hssfColor = (HSSFColor) bgColor;
//                String argbHex = hssfColor.getARGBHex();
//                if (argbHex != null) {
//                    styleNode.put("bgColor", argbHex);
//                }
//            }
//        } catch (Exception e) {
//            // 忽略颜色提取错误
//        }

        try {
            // 字体样式
            Font font = cell.getSheet().getWorkbook().getFontAt(cellStyle.getFontIndex());
            if (font != null) {
                ObjectNode fontNode = objectMapper.createObjectNode();
                fontNode.put("name", font.getFontName());
                fontNode.put("size", font.getFontHeightInPoints());
                fontNode.put("bold", font.getBold());
                fontNode.put("italic", font.getItalic());

                // 字体颜色
//                short fontColor = font.getColor();
//                if (fontColor instanceof HSSFColor) {
//                    HSSFColor hssfFontColor = (HSSFColor) fontColor;
//                    String fontColorHex = hssfFontColor.getARGBHex();
//                    if (fontColorHex != null) {
//                        fontNode.put("color", fontColorHex);
//                    }
//                }

                styleNode.set("font", fontNode);
            }
        } catch (Exception e) {
            // 忽略字体提取错误
        }

        // 对齐方式
        try {
            styleNode.put("align", cellStyle.getAlignment().toString());
            styleNode.put("valign", cellStyle.getVerticalAlignment().toString());
        } catch (Exception e) {
            // 忽略对齐方式提取错误
        }

        // 边框
        try {
            ObjectNode borderNode = objectMapper.createObjectNode();
            borderNode.put("top", getBorderStyleName(cellStyle.getBorderTop()));
            borderNode.put("bottom", getBorderStyleName(cellStyle.getBorderBottom()));
            borderNode.put("left", getBorderStyleName(cellStyle.getBorderLeft()));
            borderNode.put("right", getBorderStyleName(cellStyle.getBorderRight()));
            styleNode.set("border", borderNode);
        } catch (Exception e) {
            // 忽略边框提取错误
        }

        return styleNode;
    }

    /**
     * 创建合并单元格映射
     */
    private Map<String, CellRangeAddress> createMergeMap(List<CellRangeAddress> mergedRegions) {
        Map<String, CellRangeAddress> mergeMap = new HashMap<>();
        for (CellRangeAddress mergedRegion : mergedRegions) {
            for (int row = mergedRegion.getFirstRow(); row <= mergedRegion.getLastRow(); row++) {
                for (int col = mergedRegion.getFirstColumn(); col <= mergedRegion.getLastColumn(); col++) {
                    String key = row + "_" + col;
                    mergeMap.put(key, mergedRegion);
                }
            }
        }
        return mergeMap;
    }

    /**
     * 获取范围字符串
     */
    private String getRangeString(CellRangeAddress range) {
        return getCellAddress(range.getFirstRow(), range.getFirstColumn()) + ":" +
                getCellAddress(range.getLastRow(), range.getLastColumn());
    }

    /**
     * 获取单元格地址
     */
    private String getCellAddress(int row, int col) {
        return getColumnName(col) + (row + 1);
    }

    /**
     * 获取列名
     */
    private String getColumnName(int columnIndex) {
        StringBuilder columnName = new StringBuilder();
        while (columnIndex >= 0) {
            int remainder = columnIndex % 26;
            columnName.insert(0, (char) ('A' + remainder));
            columnIndex = (columnIndex / 26) - 1;
        }
        return columnName.toString();
    }

    /**
     * 获取最大列数
     */
    private int getMaxColumnCount(Sheet sheet) {
        int maxCols = 0;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                maxCols = Math.max(maxCols, row.getLastCellNum());
            }
        }
        return maxCols;
    }

    /**
     * 获取边框样式名称
     */
    private String getBorderStyleName(BorderStyle borderStyle) {
        if (borderStyle == null) return "NONE";
        return borderStyle.toString();
    }

    /**
     * 获取单元格值的字符串表示
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    double num = cell.getNumericCellValue();
                    if (num == (long) num) {
                        return String.valueOf((long) num);
                    } else {
                        return String.valueOf(num);
                    }
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return String.valueOf(cell.getNumericCellValue());
                } catch (Exception e) {
                    return cell.getCellFormula();
                }
            default:
                return "";
        }
    }

    /**
     * 保存JSON到文件
     */
    public void saveToFile(String jsonData, String outputPath) throws IOException {
        try (FileWriter writer = new FileWriter(outputPath)) {
            writer.write(jsonData);
        }
    }

    /**
     * 使用示例
     */
    public static void main(String[] args) {
        try {
            ExcelToJsonByTemplate1 converter = new ExcelToJsonByTemplate1();

            // 转换Excel文件
            String jsonData = converter.convertExcelByTemplate("D:\\temp\\document\\1104\\GF0401-231.xls");

            // 保存结果
            converter.saveToFile(jsonData, "table_template_output.json");

            System.out.println(jsonData);

            System.out.println("转换完成！JSON文件已生成");

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}